-- =============================================
-- COMPLETE FINAL SCRIPT
-- FIFO Work-Based Leave Accrual System
-- =============================================
-- STEP 1 : ALTER TA_Tbl_LeavePolicyDetails
-- STEP 2 : ALTER TA_Tbl_Emp_LeavePolicyDetails
-- STEP 3 : CREATE TA_Tbl_LeaveQuotaAccrual
-- STEP 4 : UPDATE Master Policy Record
-- STEP 5 : SYNC Employee Policy from Master
-- STEP 6 : Proc_TA_CalculateContinuousLeaveQuota
-- STEP 7 : Proc_TA_AttendanceAdujstedHours
-- STEP 8 : Proc_TA_GetRemainingNumberOfLeavesForSuccessiveYear
-- =============================================


-- =============================================
-- STEP 1: ADD FIFO COLUMNS TO MASTER POLICY TABLE
-- TA_Tbl_LeavePolicyDetails
-- =============================================
IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TA_Tbl_LeavePolicyDetails' AND COLUMN_NAME = 'FIFO'
)
BEGIN
    ALTER TABLE TA_Tbl_LeavePolicyDetails
    ADD FIFO INT NOT NULL DEFAULT 0
    -- 0 = Monthly Pro-Rata (original logic)
    -- 1 = Work-Based Accrual (new FIFO logic)
    PRINT 'Column FIFO added to TA_Tbl_LeavePolicyDetails'
END
ELSE
    PRINT 'Column FIFO already exists'

IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TA_Tbl_LeavePolicyDetails' AND COLUMN_NAME = 'WorkingDuration'
)
BEGIN
    ALTER TABLE TA_Tbl_LeavePolicyDetails
    ADD WorkingDuration INT NOT NULL DEFAULT 0
    -- Number of continuous days employee must work
    -- e.g. 20 (every 20 days worked earns leave)
    PRINT 'Column WorkingDuration added to TA_Tbl_LeavePolicyDetails'
END
ELSE
    PRINT 'Column WorkingDuration already exists'

IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TA_Tbl_LeavePolicyDetails' AND COLUMN_NAME = 'LeaveDuration'
)
BEGIN
    ALTER TABLE TA_Tbl_LeavePolicyDetails
    ADD LeaveDuration INT NOT NULL DEFAULT 0
    -- Leave days credited per completed working block
    -- e.g. 10 (20 continuous days worked = 10 days leave)
    PRINT 'Column LeaveDuration added to TA_Tbl_LeavePolicyDetails'
END
ELSE
    PRINT 'Column LeaveDuration already exists'

GO


-- =============================================
-- STEP 2: ADD FIFO COLUMNS TO EMPLOYEE POLICY TABLE
-- TA_Tbl_Emp_LeavePolicyDetails
-- =============================================
IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TA_Tbl_Emp_LeavePolicyDetails' AND COLUMN_NAME = 'FIFO'
)
BEGIN
    ALTER TABLE TA_Tbl_Emp_LeavePolicyDetails
    ADD FIFO INT NOT NULL DEFAULT 0
    PRINT 'Column FIFO added to TA_Tbl_Emp_LeavePolicyDetails'
END
ELSE
    PRINT 'Column FIFO already exists in Emp table'

IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TA_Tbl_Emp_LeavePolicyDetails' AND COLUMN_NAME = 'WorkingDuration'
)
BEGIN
    ALTER TABLE TA_Tbl_Emp_LeavePolicyDetails
    ADD WorkingDuration INT NOT NULL DEFAULT 0
    PRINT 'Column WorkingDuration added to TA_Tbl_Emp_LeavePolicyDetails'
END
ELSE
    PRINT 'Column WorkingDuration already exists in Emp table'

IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TA_Tbl_Emp_LeavePolicyDetails' AND COLUMN_NAME = 'LeaveDuration'
)
BEGIN
    ALTER TABLE TA_Tbl_Emp_LeavePolicyDetails
    ADD LeaveDuration INT NOT NULL DEFAULT 0
    PRINT 'Column LeaveDuration added to TA_Tbl_Emp_LeavePolicyDetails'
END
ELSE
    PRINT 'Column LeaveDuration already exists in Emp table'

GO


-- =============================================
-- STEP 3: CREATE QUOTA ACCRUAL HISTORY TABLE
-- Tracks every block of 20 days worked
-- and the 10 days quota credited per block
-- =============================================
IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'TA_Tbl_LeaveQuotaAccrual'
)
BEGIN
    CREATE TABLE TA_Tbl_LeaveQuotaAccrual
    (
        Accrual_Id          INT IDENTITY(1,1)  PRIMARY KEY,
        Employee_Id         INT               NOT NULL,
        LeaveType_Id        INT               NOT NULL,
        YearSettings_Id     INT               NOT NULL,
        AccrualDate         DATETIME          NOT NULL,   -- Date quota was credited
        WorkingDaysCount    INT               NOT NULL,   -- Always = WorkingDuration (e.g. 20)
        QuotaDaysCredited   DECIMAL(18,1)     NOT NULL,   -- Always = LeaveDuration (e.g. 10)
        BlockNumber         INT               NOT NULL,   -- 1st block, 2nd block, 3rd block...
        FromDate            DATETIME          NOT NULL,   -- Streak start date
        ToDate              DATETIME          NOT NULL,   -- Streak completion date (day 20)
        IsDeleted           INT               NOT NULL DEFAULT 0,
        CreatedOn           DATETIME          NOT NULL DEFAULT GETDATE()
    )
    PRINT 'Table TA_Tbl_LeaveQuotaAccrual created successfully'
END
ELSE
    PRINT 'Table TA_Tbl_LeaveQuotaAccrual already exists'

GO


-- =============================================
-- STEP 4: UPDATE MASTER POLICY RECORD
-- Set FIFO=1, WorkingDuration=20, LeaveDuration=10
-- Update PolicyDetails_Id as per your data
-- =============================================
UPDATE TA_Tbl_LeavePolicyDetails
SET
    FIFO            = 1,   -- Enable work-based accrual
    WorkingDuration = 20,  -- Every 20 continuous working days
    LeaveDuration   = 10   -- Earns 10 days leave quota
WHERE PolicyDetails_Id = 12  -- *** Change to your PolicyDetails_Id ***

PRINT 'Master policy updated: PolicyDetails_Id = 12'
GO


-- =============================================
-- STEP 5: SYNC EMPLOYEE POLICY FROM MASTER
-- Copies FIFO settings to all assigned employees
-- Run once after Step 4
-- =============================================
UPDATE ELPD
SET
    ELPD.FIFO            = LPD.FIFO,
    ELPD.WorkingDuration = LPD.WorkingDuration,
    ELPD.LeaveDuration   = LPD.LeaveDuration
FROM TA_Tbl_Emp_LeavePolicyDetails ELPD
INNER JOIN TA_Tbl_Emp_LeavePolicy    ELP ON ELP.Emp_LeavePolicyId = ELPD.Emp_LeavePolicyId
INNER JOIN TA_Tbl_LeavePolicyDetails LPD ON LPD.Policy_Id         = ELP.LeavePolicy_Id
                                        AND LPD.LeaveType_Id      = ELPD.LeaveType_Id

PRINT 'Employee policy details synced from master policy'
GO


-- =============================================
-- STEP 6: Proc_TA_CalculateContinuousLeaveQuota
-- Core Logic:
--   Scans TA_Tbl_ProcessedData day by day
--   Counts continuous present days (streak)
--   Every WorkingDuration (20) days → credits
--   LeaveDuration (10) days to quota
--   Absent/Leave day → streak resets to 0
--   Half day present (TotalDays=0.5) → streak +0.5
-- =============================================
IF OBJECT_ID('dbo.Proc_TA_CalculateContinuousLeaveQuota','P') IS NOT NULL
    DROP PROCEDURE dbo.Proc_TA_CalculateContinuousLeaveQuota
GO

USE [JacRijk]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_CalculateContinuousLeaveQuota]    Script Date: 06-03-2026 18:23:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Proc_TA_CalculateContinuousLeaveQuota]
    @Employee_Id     INT,
    @LeaveType_Id    INT,
    @YearSettingId   INT
AS
BEGIN
    SET NOCOUNT ON;

    -- =============================================
    -- VARIABLE DECLARATIONS
    -- =============================================
    DECLARE @WorkingDuration    INT             -- From LeavePolicyDetails (e.g. 20)
    DECLARE @LeaveDuration      DECIMAL(18,1)   -- From LeavePolicyDetails (e.g. 10)
    DECLARE @YearFromDate       DATETIME        -- Year start date
    DECLARE @ScanFromDate       DATETIME        -- Where to resume scanning
    DECLARE @LastBlockNumber    INT             -- Last credited block number
    DECLARE @LastAccrualToDate  DATETIME        -- Last block's end date
    DECLARE @StreakCount        DECIMAL(18,1)   -- Running streak counter
    DECLARE @StreakStartDate    DATETIME        -- When current streak began
    DECLARE @EmployeeCodeId     NVARCHAR(100)   -- SwipeCardNo for ProcessedData join
    DECLARE @DayContribution    DECIMAL(18,1)   -- How much today adds to streak

    -- Cursor row variables from ProcessedData
    DECLARE @CurrDate           DATETIME
    DECLARE @CurrAttendance     NVARCHAR(50)    -- Overall: 'Present','Absent','Leave'
    DECLARE @CurrFNCode         NVARCHAR(50)    -- Forenoon session code
    DECLARE @CurrANCode         NVARCHAR(50)    -- Afternoon session code
    DECLARE @CurrTotalDays      DECIMAL(18,1)   -- 1.0=Full | 0.5=Half | 0.0=Absent

    -- =============================================
    -- FETCH EMPLOYEE SWIPE CODE
    -- ProcessedData uses Employee_Code_Id = SwipeCardNo
    -- =============================================
    SELECT @EmployeeCodeId = SwipeCardNo
    FROM TA_Tbl_Employee
    WHERE Employee_Id   = @Employee_Id
      AND IsDeleted     = 0

    IF @EmployeeCodeId IS NULL
    BEGIN
        PRINT 'ERROR: SwipeCardNo not found for Employee_Id: ' + CAST(@Employee_Id AS NVARCHAR)
        RETURN
    END

    -- =============================================
    -- FETCH YEAR START DATE
    -- =============================================
    SELECT @YearFromDate = FromDate
    FROM ta_tbl_yearsetting
    WHERE YearSettings_Id = @YearSettingId

    IF @YearFromDate IS NULL
    BEGIN
        PRINT 'ERROR: Year setting not found for YearSettingId: ' + CAST(@YearSettingId AS NVARCHAR)
        RETURN
    END

    -- =============================================
    -- FETCH FIFO POLICY SETTINGS
    -- From TA_Tbl_LeavePolicyDetails (Master)
    -- =============================================
    SELECT TOP 1
        @WorkingDuration = LPD.WorkingDuration,
        @LeaveDuration   = LPD.LeaveDuration
    FROM TA_Tbl_Emp_LeavePolicy ELP
    INNER JOIN TA_Tbl_LeavePolicyDetails LPD
        ON  LPD.Policy_Id      = ELP.LeavePolicy_Id
        AND LPD.LeaveType_Id   = @LeaveType_Id
    WHERE ELP.EmployeeId       = @Employee_Id
      AND ELP.YearSettingId    = @YearSettingId
      AND LPD.FIFO             = 1
      AND LPD.WorkingDuration  > 0
      AND LPD.LeaveDuration    > 0

    IF @WorkingDuration IS NULL OR @LeaveDuration IS NULL
    BEGIN
        PRINT 'ERROR: No FIFO policy found for Employee: '
            + CAST(@Employee_Id AS NVARCHAR)
            + ' | LeaveType: ' + CAST(@LeaveType_Id AS NVARCHAR)
        RETURN
    END

    -- =============================================
    -- FIND RESUME POINT
    -- Resume from day after last credited block
    -- First run: starts from year begin date
    -- =============================================
    SELECT
        @LastBlockNumber   = ISNULL(MAX(BlockNumber), 0),
        @LastAccrualToDate = MAX(ToDate)
    FROM TA_Tbl_LeaveQuotaAccrual
    WHERE Employee_Id     = @Employee_Id
      AND LeaveType_Id    = @LeaveType_Id
      AND YearSettings_Id = @YearSettingId
      AND IsDeleted       = 0

    SET @ScanFromDate = CASE
        WHEN @LastAccrualToDate IS NOT NULL
             THEN DATEADD(day, 1, @LastAccrualToDate)
        ELSE @YearFromDate
    END

    -- =============================================
    -- INITIALIZE STREAK
    -- =============================================
    SET @StreakCount     = 0
    SET @StreakStartDate = NULL

    -- =============================================
    -- CURSOR: Loop TA_Tbl_ProcessedData day by day
    -- Ordered ASC to maintain streak sequence
    -- =============================================
    DECLARE cur_Att CURSOR FOR
        SELECT
            PD.[Date],
            PD.Attendance,
            PD.FNAttendanceCode,
            PD.ANAttendanceCode,
            PD.TotalDays
        FROM TA_Tbl_ProcessedData PD
        WHERE PD.Employee_Code_Id  = @EmployeeCodeId
          AND PD.[Date]           >= @ScanFromDate
          AND PD.[Date]           <= GETDATE()
          AND PD.YearSettingId     = @YearSettingId
        ORDER BY PD.[Date] ASC

    OPEN cur_Att
    FETCH NEXT FROM cur_Att
        INTO @CurrDate, @CurrAttendance, @CurrFNCode, @CurrANCode, @CurrTotalDays

    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- =============================================
        -- DETERMINE DAY CONTRIBUTION TO STREAK
        --
        -- FN=Present, AN=Present → TotalDays=1.0 → +1.0
        -- FN=Present, AN=Absent  → TotalDays=0.5 → +0.5
        -- FN=Absent,  AN=Present → TotalDays=0.5 → +0.5
        -- FN=Present, AN=Leave   → TotalDays=0.5 → +0.5
        -- FN=Leave,   AN=Present → TotalDays=0.5 → +0.5
        -- FN=Absent,  AN=Absent  → TotalDays=0.0 → RESET
        -- FN=Leave,   AN=Leave   → TotalDays=0.0 → RESET
        -- =============================================
        SET @DayContribution = 0

        -- Full present day
        IF @CurrTotalDays = 1.0 AND @CurrAttendance = 'Present'
        BEGIN
            SET @DayContribution = 1.0
        END

        -- Half day: at least one session is Present
        ELSE IF @CurrTotalDays = 0.5
             AND (@CurrFNCode = 'Present' OR @CurrANCode = 'Present')
        BEGIN
            SET @DayContribution = 0.5
        END

        -- Full absent / full leave / no work done
        ELSE
        BEGIN
            SET @DayContribution = 0  -- Triggers streak reset below
        END

        -- =============================================
        -- APPLY TO STREAK
        -- =============================================
        IF @DayContribution > 0
        BEGIN
            -- Mark streak start on first working day
            IF @StreakStartDate IS NULL
                SET @StreakStartDate = @CurrDate

            SET @StreakCount = @StreakCount + @DayContribution

            -- =============================================
            -- BLOCK COMPLETE CHECK
            -- e.g. StreakCount reached 20 days
            -- =============================================
            IF @StreakCount >= @WorkingDuration
            BEGIN
                SET @LastBlockNumber = @LastBlockNumber + 1

                -- Credit quota to accrual table
                INSERT INTO TA_Tbl_LeaveQuotaAccrual
                (
                    Employee_Id,
                    LeaveType_Id,
                    YearSettings_Id,
                    AccrualDate,
                    WorkingDaysCount,
                    QuotaDaysCredited,
                    BlockNumber,
                    FromDate,
                    ToDate,
                    IsDeleted,
                    CreatedOn
                )
                VALUES
                (
                    @Employee_Id,
                    @LeaveType_Id,
                    @YearSettingId,
                    GETDATE(),           -- Date quota was credited
                    @WorkingDuration,    -- 20 days worked
                    @LeaveDuration,      -- 10 days credited
                    @LastBlockNumber,    -- Block sequence number
                    @StreakStartDate,    -- Streak started on
                    @CurrDate,           -- Streak completed on
                    0,
                    GETDATE()
                )

                PRINT 'Block ' + CAST(@LastBlockNumber AS NVARCHAR)
                    + ' | +' + CAST(@LeaveDuration AS NVARCHAR) + ' days credited'
                    + ' | From: ' + CONVERT(NVARCHAR, @StreakStartDate, 103)
                    + ' To: '     + CONVERT(NVARCHAR, @CurrDate, 103)

                -- Reset streak counter for next block
                SET @StreakCount     = 0
                SET @StreakStartDate = NULL
            END
        END
        ELSE
        BEGIN
            -- Streak broken: reset everything
            IF @StreakCount > 0
                PRINT 'Streak RESET on: ' + CONVERT(NVARCHAR, @CurrDate, 103)
                    + ' | FN: '      + ISNULL(@CurrFNCode, 'NULL')
                    + ' | AN: '      + ISNULL(@CurrANCode, 'NULL')
                    + ' | Streak was at: ' + CAST(@StreakCount AS NVARCHAR) + ' days'

            SET @StreakCount     = 0
            SET @StreakStartDate = NULL
        END

        FETCH NEXT FROM cur_Att
            INTO @CurrDate, @CurrAttendance, @CurrFNCode, @CurrANCode, @CurrTotalDays

    END -- END WHILE

    CLOSE cur_Att
    DEALLOCATE cur_Att

    PRINT '-------------------------------------------'
    PRINT 'Completed | Employee: '  + CAST(@Employee_Id AS NVARCHAR)
        + ' | LeaveType: '          + CAST(@LeaveType_Id AS NVARCHAR)
        + ' | Blocks Credited: '    + CAST(@LastBlockNumber AS NVARCHAR)
        + ' | Total Quota: '        + CAST(@LastBlockNumber * @LeaveDuration AS NVARCHAR) + ' days'
    PRINT '-------------------------------------------'

END


END
GO


-- =============================================
-- STEP 7: Proc_TA_AttendanceAdujstedHours
-- Runs every 3 minutes via SQL Agent Job
-- Reads FIFO leave types from master policy
-- Smart skip: only processes if new attendance
-- data exists beyond the last accrual block
-- =============================================

USE [JacRijk]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_AttendanceAdujstedHours]    Script Date: 06-03-2026 18:20:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Proc_TA_AttendanceAdujstedHours 'M0001','2026-01-07',9
ALTER PROCEDURE [dbo].[Proc_TA_AttendanceAdujstedHours]
    @EmployeeCodeId NVARCHAR(100),
    @SHIFTDATE      DATETIME,
    @CompanyId      INT = 1
AS
BEGIN
    SET NOCOUNT ON;

    -- =============================================
    -- VARIABLE DECLARATIONS
    -- =============================================
    DECLARE @Active_Year        INT
    DECLARE @EmployeeAutoId     INT
    DECLARE @LeavePolicy_Id     INT
    DECLARE @leaveType_Id       INT
    DECLARE @i                  INT
    DECLARE @count              INT
    DECLARE @WorkingDuration    INT
    DECLARE @LeaveDuration      INT
    DECLARE @LastAccrualDate    DATETIME
    DECLARE @LatestAttendDate   DATETIME

    -- =============================================
    -- FETCH EMPLOYEE AUTO ID FROM SWIPE CARD NO
    -- =============================================
    SELECT @EmployeeAutoId = Employee_Id
    FROM TA_Tbl_Employee
    WHERE SwipeCardNo     = @EmployeeCodeId
      AND IsDeleted       = 0
      AND Employee_Status = 1

    IF @EmployeeAutoId IS NULL
    BEGIN
        PRINT 'Employee not found for SwipeCardNo: ' + @EmployeeCodeId
        RETURN
    END

    -- =============================================
    -- FETCH ACTIVE YEAR SETTING
    -- =============================================
    SELECT @Active_Year = YearSettings_Id
    FROM TA_Tbl_YearSetting
    WHERE IsActive = 1

    IF @Active_Year IS NULL
    BEGIN
        PRINT 'No active year setting found.'
        RETURN
    END

    -- =============================================
    -- FETCH EMPLOYEE LEAVE POLICY
    -- =============================================
    SELECT @LeavePolicy_Id = LeavePolicy_Id
    FROM TA_Tbl_Emp_LeavePolicy
    WHERE YearSettingId = @Active_Year
      AND EmployeeId    = @EmployeeAutoId

    IF @LeavePolicy_Id IS NULL
    BEGIN
        PRINT 'No leave policy found for Employee: ' + CAST(@EmployeeAutoId AS NVARCHAR)
        RETURN
    END

    -- =============================================
    -- TEMP TABLE: FIFO=1 Leave Types Only
    -- Reads from TA_Tbl_LeavePolicyDetails (Master)
    -- Only processes work-based accrual types
    -- Skips monthly pro-rata leave types entirely
    -- =============================================
    DECLARE @tempTbl TABLE
    (
        id               INT IDENTITY(1,1),
        leaveTypeId      INT,
        WorkingDuration  INT,   -- e.g. 20
        LeaveDuration    INT    -- e.g. 10
    )

    INSERT INTO @tempTbl (leaveTypeId, WorkingDuration, LeaveDuration)
    SELECT
        LeaveType_Id,
        WorkingDuration,
        LeaveDuration
    FROM TA_Tbl_LeavePolicyDetails
    WHERE Policy_Id        = @LeavePolicy_Id
      AND FIFO             = 1    -- Only work-based types
      AND WorkingDuration  > 0
      AND LeaveDuration    > 0

    -- FIX: Correctly set @count (was NULL before)
    SELECT @count = COUNT(1) FROM @tempTbl

    IF @count = 0
    BEGIN
        PRINT 'No FIFO leave types found for Employee: ' + CAST(@EmployeeAutoId AS NVARCHAR)
        RETURN
    END

    -- =============================================
    -- LOOP: Process each FIFO leave type
    -- FIX: Start at 1 (IDENTITY starts at 1)
    -- =============================================
    SET @i = 1

    WHILE @i <= @count
    BEGIN

        SELECT
            @leaveType_Id    = leaveTypeId,
            @WorkingDuration = WorkingDuration,
            @LeaveDuration   = LeaveDuration
        FROM @tempTbl
        WHERE id = @i

        IF @leaveType_Id IS NOT NULL
        BEGIN

            -- Reset per iteration
            SET @LastAccrualDate  = NULL
            SET @LatestAttendDate = NULL

            -- =============================================
            -- SMART SKIP CHECK
            -- Get last credited block end date
            -- =============================================
            SELECT @LastAccrualDate = MAX(ToDate)
            FROM TA_Tbl_LeaveQuotaAccrual
            WHERE Employee_Id     = @EmployeeAutoId
              AND LeaveType_Id    = @leaveType_Id
              AND YearSettings_Id = @Active_Year
              AND IsDeleted       = 0

            -- =============================================
            -- Get latest present day from ProcessedData
            -- Using TotalDays > 0 to include half days
            -- =============================================
            SELECT @LatestAttendDate = MAX(PD.[Date])
            FROM TA_Tbl_ProcessedData PD
            WHERE PD.Employee_Code_Id = @EmployeeCodeId
              AND (
                    -- Full present day
                    (PD.Attendance = 'Present' AND PD.TotalDays = 1.0)
                    OR
                    -- Half day: at least one session present
                    (PD.TotalDays = 0.5 AND (PD.FNAttendanceCode = 'Present' OR PD.ANAttendanceCode = 'Present'))
                  )
              AND PD.YearSettingId = @Active_Year

            -- =============================================
            -- Only call quota SP if new attendance exists
            -- beyond the last credited block
            -- Prevents redundant calls every 3 minutes
            -- =============================================
            IF (@LastAccrualDate IS NULL OR @LatestAttendDate > @LastAccrualDate)
            BEGIN
                PRINT 'Processing LeaveType: ' + CAST(@leaveType_Id AS NVARCHAR)
                    + ' | Employee: '          + CAST(@EmployeeAutoId AS NVARCHAR)
                    + ' | New data from: '     + CONVERT(NVARCHAR, @LatestAttendDate, 103)

					Select 'Yes'

                EXECUTE Proc_TA_CalculateContinuousLeaveQuota
                    @Employee_Id   = @EmployeeAutoId,
                    @LeaveType_Id  = @leaveType_Id,
                    @YearSettingId = @Active_Year
            END
            ELSE
            BEGIN
                PRINT 'Skipping LeaveType: ' + CAST(@leaveType_Id AS NVARCHAR)
                    + ' | No new attendance data beyond last accrual: '
                    + CONVERT(NVARCHAR, @LastAccrualDate, 103)
            END

        END

        SET @i = @i + 1
    END -- END WHILE

    PRINT 'Attendance processing done for Employee: ' + CAST(@EmployeeAutoId AS NVARCHAR)

END



-- =============================================
-- STEP 8: Proc_TA_GetRemainingNumberOfLeavesForSuccessiveYear
-- FIFO block reads total quota from accrual table
-- Monthly pro-rata logic preserved unchanged
-- =============================================
IF OBJECT_ID('dbo.Proc_TA_GetRemainingNumberOfLeavesForSuccessiveYear','P') IS NOT NULL
    DROP PROCEDURE dbo.Proc_TA_GetRemainingNumberOfLeavesForSuccessiveYear
GO


ALTER PROCEDURE [dbo].[Proc_TA_GetRemainingNumberOfLeavesForSuccessiveYear]
    @Employee_Id     INT,
    @LeaveType_Id    INT,
    @YearSettingId   INT
AS
BEGIN
    SET NOCOUNT ON;

    -- =============================================
    -- VARIABLE DECLARATIONS
    -- =============================================
    DECLARE @LeaveApplicationCount     DECIMAL(18,1)
    DECLARE @LOPCount                  DECIMAL(18,1)
    DECLARE @CurrentyearSettingId      INT
    DECLARE @SuccessiveYearSettingId   INT
    DECLARE @SuccesiveLeavepolicyCount FLOAT
    DECLARE @MaxapplicableDays         FLOAT
    DECLARE @CurrentYearStartDate      DATETIME
    DECLARE @isWFH                     INT
    DECLARE @IsProrataRequired         INT
    DECLARE @IsDocProofRequired        INT
    DECLARE @DateofJoining             DATETIME

    -- FIFO variables
    DECLARE @FIFO                      INT
    DECLARE @WorkingDuration           INT
    DECLARE @LeaveDuration             INT
    DECLARE @EarnedQuota               DECIMAL(18,1)
    DECLARE @CarryForward              DECIMAL(18,1)
    DECLARE @LeavesAvailed             DECIMAL(18,1)
    DECLARE @RemainingLeave            DECIMAL(18,1)
    DECLARE @Emp_LeavePolicyId         INT
    DECLARE @Policy_Id                 INT

    -- =============================================
    -- FETCH BASE YEAR SETTINGS
    -- =============================================
    SELECT
        @CurrentyearSettingId  = YearSettings_Id,
        @CurrentYearStartDate  = FromDate
    FROM ta_tbl_yearsetting
    WHERE IsActive = 1

    SELECT TOP 1
        @SuccessiveYearSettingId = YearSettings_Id
    FROM ta_tbl_yearsetting
    WHERE YearSettings_Id > (
        SELECT YearSettings_Id FROM ta_tbl_yearsetting WHERE IsActive = 1
    )

    -- =============================================
    -- FETCH EMPLOYEE DATE OF JOINING
    -- =============================================
    SELECT @DateofJoining = DateOfJoining
    FROM TA_Tbl_Employee
    WHERE Employee_Id     = @Employee_Id
      AND IsDeleted       = 0
      AND Employee_Status = 1

    -- Fetch Employee Leave Policy IDs (used in both branches)
    SELECT
        @Emp_LeavePolicyId = LP.Emp_LeavePolicyId,
        @Policy_Id         = LP.LeavePolicy_Id
    FROM TA_Tbl_Emp_LeavePolicy LP
    WHERE LP.EmployeeId    = @Employee_Id
      AND LP.YearSettingId = @YearSettingId


    -- =============================================
    -- SPECIFIC LEAVE TYPE (@LeaveType_Id <> -1)
    -- Validates: PreviousYear Quota + CurrentYear Quota
    --            - CurrentMonth Accrual - Taken Leaves
    -- =============================================
    IF (@LeaveType_Id <> -1)
    BEGIN

        SELECT
            @isWFH              = isWFH,
            @IsProrataRequired  = IsProrataRequired,
            @IsDocProofRequired = IsDocProofRequired
        FROM TA_Tbl_LeaveType
        WHERE LeaveType_Id = @LeaveType_Id

        -- READ FIFO SETTINGS FROM TA_Tbl_LeavePolicyDetails (MASTER)
        SELECT
            @FIFO            = ISNULL(LPD.FIFO, 0),
            @WorkingDuration = ISNULL(LPD.WorkingDuration, 0),
            @LeaveDuration   = ISNULL(LPD.LeaveDuration, 0)
        FROM TA_Tbl_LeavePolicyDetails LPD
        INNER JOIN TA_Tbl_Emp_LeavePolicy ELP ON ELP.LeavePolicy_Id = LPD.Policy_Id
        WHERE ELP.Emp_LeavePolicyId = @Emp_LeavePolicyId
          AND LPD.LeaveType_Id      = @LeaveType_Id

        -- FETCH AVAILED AND LOP COUNTS
        SELECT
            @LeaveApplicationCount = SUM(la.NumberOfDays)
        FROM Ta_Tbl_LeaveApplication la
        INNER JOIN TA_Tbl_YearSetting ys ON ys.YearSettings_Id = la.YearSettings_Id
        WHERE ys.YearSettings_Id = @YearSettingId
          AND la.LeaveType_Id    = @LeaveType_Id
          AND la.Employee_Id     = @Employee_Id
          AND la.Type        NOT IN ('Restricted Holiday','Comp Off','ESI','LOP')
          AND la.Status      NOT IN ('Rejected')
          AND la.IsDeleted       = 0
          AND la.IsBorrow        = 0

        SELECT
            @LOPCount = SUM(la.NumberOfDays)
        FROM Ta_Tbl_LeaveApplication la
        INNER JOIN TA_Tbl_YearSetting ys ON ys.YearSettings_Id = la.YearSettings_Id
        WHERE ys.YearSettings_Id = @YearSettingId
          AND la.LeaveType_Id    = @LeaveType_Id
          AND la.Employee_Id     = @Employee_Id
          AND la.Type        IN ('LOP')
          AND la.Status      NOT IN ('Rejected')
          AND la.IsDeleted       = 0
          AND la.IsBorrow        = 0

        -- =============================================
        -- BRANCH A: FIFO=1 → WORK-BASED ACCRUAL
        -- Quota = carry_PrevYear + SUM(QuotaDaysCredited)
        -- Remaining = Quota - LeavesAvailed
        -- =============================================
        IF (@FIFO = 1 AND @WorkingDuration > 0 AND @LeaveDuration > 0)
        BEGIN
            -- Fetch carry forward from previous year closure
            SELECT @CarryForward = ISNULL(carry_PrevYear, 0)
            FROM TA_tbl_Yearclosure
            WHERE Employee_Id    = @Employee_Id
              AND LeaveTypeId    = @LeaveType_Id
              AND Year_SettingID = @YearSettingId

            IF @CarryForward IS NULL
                SET @CarryForward = 0

            -- Fetch current year accrued quota
            SELECT
                @EarnedQuota = ISNULL(SUM(QuotaDaysCredited), 0)
            FROM TA_Tbl_LeaveQuotaAccrual
            WHERE Employee_Id     = @Employee_Id
              AND LeaveType_Id    = @LeaveType_Id
              AND YearSettings_Id = @YearSettingId
              AND IsDeleted       = 0

            -- Total available = PreviousYear carry + CurrentYear earned quota
            SET @EarnedQuota = @CarryForward + ISNULL(@EarnedQuota, 0)

            -- Total leaves availed in current year
            SELECT
                @LeavesAvailed = ISNULL(SUM(NumberOfDays), 0)
            FROM Ta_Tbl_LeaveApplication
            WHERE Employee_Id     = @Employee_Id
              AND LeaveType_Id    = @LeaveType_Id
              AND YearSettings_Id = @YearSettingId
              AND Status      NOT IN ('Rejected')
              AND IsDeleted       = 0
              AND IsBorrow        = 0
              AND Type        NOT IN ('Restricted Holiday','Comp Off','ESI','LOP')

            -- Remaining = Total Quota - Availed
            SET @RemainingLeave = @EarnedQuota - ISNULL(@LeavesAvailed, 0)
            IF @RemainingLeave < 0
                SET @RemainingLeave = 0

            SELECT
                @EarnedQuota                            AS RemainingNoOfLeavesCurrentYear,
                ISNULL(@LeavesAvailed, 0)               AS availed,
                ISNULL(@LOPCount, 0)                    AS LOPavailed,
                @RemainingLeave                         AS RemainingNoOfLeaves,
                ISNULL(@isWFH, 0)                       AS isWFH,
                ISNULL(@IsProrataRequired, 1)           AS IsProrataRequired,
                ISNULL(@IsDocProofRequired, 0)          AS IsDocProofRequired
        END

        -- =============================================
        -- BRANCH B: FIFO=0 → ORIGINAL MONTHLY PRO-RATA
        -- Quota = carry_PrevYear + Current_Quota
        -- Remaining = ProRataMonthly - Availed + carry_PrevYear
        -- =============================================
        ELSE
        BEGIN
            IF (@YearSettingId = @CurrentyearSettingId)
            BEGIN
                IF (@LeaveApplicationCount > 0)
                BEGIN
                    SELECT
                        (ISNULL(yc.carry_PrevYear,0.0) + ISNULL(yc.Current_Quota,0.0))
                        -(ISNULL(SUM(la.NumberOfDays),0.0) + ISNULL(yc.Encashment,0.0))    AS RemainingNoOfLeavesCurrentYear,
                        ISNULL(@LeaveApplicationCount,0)    AS availed,
                        ISNULL(@LOPCount,0)                 AS LOPavailed,
                        CASE
                            WHEN (
                                CASE WHEN DATEPART(d,@DateofJoining)>15 THEN
                                    CASE
                                        WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0
                                        WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days
                                        ELSE CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month
                                    END
                                ELSE
                                    CASE
                                        WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0
                                        WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days
                                        ELSE CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month
                                    END
                                END
                                -(ISNULL(SUM(la.NumberOfDays),0.0)+ISNULL(yc.Encashment,0.0))
                                -ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0)
                            )<=0 THEN 0
                        ELSE
                            CASE WHEN DATEPART(d,@DateofJoining)>15 THEN
                                CASE
                                    WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0
                                    WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days
                                    ELSE CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month
                                END
                            ELSE
                                CASE
                                    WHEN LPD.No_Of_Days<=(CASE WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0 WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month END) THEN LPD.No_Of_Days
                                    ELSE CASE WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0 WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month END
                                END
                            END
                            -(ISNULL(SUM(la.NumberOfDays),0.0)+ISNULL(yc.Encashment,0.0))
                            -ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0)
                            +ISNULL(ltd.PreviousYearBalance,0.0)
                        END                                 AS RemainingNoOfLeaves,
                        ISNULL(@isWFH,0)                    AS isWFH,
                        ISNULL(@IsProrataRequired,1)        AS IsProrataRequired,
                        ISNULL(@IsDocProofRequired,0)       AS IsDocProofRequired
                    FROM TA_tbl_Yearclosure yc
                    INNER JOIN TA_Tbl_YearSetting ys             ON yc.Year_SettingID=ys.YearSettings_Id
                    INNER JOIN Ta_Tbl_LeaveApplication la         ON la.LeaveType_Id=yc.LeaveTypeId AND yc.employee_Id=la.Employee_id AND la.YearSettings_Id=ys.YearSettings_Id
                    INNER JOIN TA_Tbl_Emp_LeavePolicy LP          ON LP.EmployeeId=yc.Employee_Id AND LP.YearSettingId=yc.Year_SettingID
                    INNER JOIN TA_Tbl_Emp_LeavePolicyDetails LPD  ON LP.LeavePolicy_Id=LPD.Policy_ID AND LP.Emp_LeavePolicyId=LPD.Emp_LeavePolicyId AND LPD.LeaveType_Id=yc.LeaveTypeID
                    LEFT OUTER JOIN ta_tbl_emp_LeaveTypedetails LTD ON LP.EmployeeId=LTD.EmployeeId AND LTD.LeaveTypeId=LPD.LeaveType_Id AND yc.Year_SettingID=LTD.YearSettingsId
                    WHERE yc.employee_Id=@Employee_Id AND LPD.LeaveType_Id=@LeaveType_Id
                      AND la.employee_Id=@Employee_Id AND yc.LeaveTypeId=@LeaveType_Id
                      AND la.LeaveType_Id=@LeaveType_Id AND ys.YearSettings_Id=@YearSettingId
                      AND la.Isdeleted=0 AND la.Status IN ('Pending','Forwarded','Approved')
                      AND la.Type NOT IN ('Restricted Holiday','Comp Off','ESI','LOP')
                      AND la.IsBorrow=0
                    GROUP BY yc.LeaveTypeId,yc.Carry_PrevYear,yc.Current_Quota,
                        yc.Borrow_NextYear,yc.Encashment,LPD.MaxDays_Month,
                        ys.FromDate,LPD.No_Of_Days,ltd.NoOffLeaveAvailed,ltd.PreviousYearBalance
                END
                ELSE
                BEGIN
                    SELECT
                        (ISNULL(yc.carry_PrevYear,0.0)+ISNULL(yc.Current_Quota,0.0))       AS RemainingNoOfLeavesCurrentYear,
                        ISNULL(@LeaveApplicationCount,0)    AS availed,
                        ISNULL(@LOPCount,0)                 AS LOPavailed,
                        CASE
                            WHEN (
                                CASE WHEN DATEPART(d,@DateofJoining)>15 THEN
                                    CASE
                                        WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))<0 THEN 0
                                        WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days
                                        ELSE CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0)
                                    END
                                ELSE
                                    CASE
                                        WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))<0 THEN 0
                                        WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days
                                        ELSE CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0)
                                    END
                                END
                                -ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0)
                            )<=0 THEN 0
                        ELSE
                            CASE WHEN DATEPART(d,@DateofJoining)>15 THEN
                                CASE
                                    WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))<0 THEN 0
                                    WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days
                                    ELSE CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0)
                                END
                            ELSE
                                CASE
                                    WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))<0 THEN 0
                                    WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days
                                    ELSE CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0)
                                END
                            END
                            -ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0)
                            +ISNULL(ltd.PreviousYearBalance,0.0)
                        END                                 AS RemainingNoOfLeaves,
                        ISNULL(@isWFH,0)                    AS isWFH,
                        ISNULL(@IsProrataRequired,1)        AS IsProrataRequired,
                        ISNULL(@IsDocProofRequired,0)       AS IsDocProofRequired
                    FROM TA_tbl_Yearclosure yc
                    INNER JOIN TA_Tbl_YearSetting ys             ON yc.Year_SettingID=ys.YearSettings_Id
                    INNER JOIN TA_Tbl_Emp_LeavePolicy LP          ON LP.EmployeeId=yc.Employee_Id AND LP.YearSettingId=yc.Year_SettingID
                    INNER JOIN TA_Tbl_Emp_LeavePolicyDetails LPD  ON LP.LeavePolicy_Id=LPD.Policy_ID AND LP.Emp_LeavePolicyId=LPD.Emp_LeavePolicyId AND LPD.LeaveType_Id=yc.LeaveTypeID
                    LEFT OUTER JOIN ta_tbl_emp_LeaveTypedetails LTD ON LP.EmployeeId=LTD.EmployeeId AND LTD.LeaveTypeId=LPD.LeaveType_Id AND yc.Year_SettingID=LTD.YearSettingsId
                    WHERE yc.employee_Id=@Employee_Id AND yc.LeaveTypeId=@LeaveType_Id
                      AND ys.YearSettings_Id=@YearSettingId
                    GROUP BY yc.LeaveTypeId,yc.Carry_PrevYear,yc.Current_Quota,
                        yc.Borrow_NextYear,yc.Encashment,LPD.MaxDays_Month,
                        ys.FromDate,LPD.No_Of_Days,ltd.NoOffLeaveAvailed,ltd.PreviousYearBalance
                END
            END
            ELSE IF (@YearSettingId = @SuccessiveYearSettingId)
            BEGIN
                SELECT TOP 1
                    @MaxapplicableDays = MaxDays_applicable
                FROM ta_tbl_leavepolicydetails
                WHERE Policy_Id = (
                    SELECT TOP 1 LeavePolicy_Id FROM ta_tbl_emp_leavepolicy
                    WHERE EmployeeId=@Employee_Id AND LeaveType_Id=@LeaveType_Id
                    ORDER BY ToDate DESC
                )

                SELECT @SuccesiveLeavepolicyCount = SUM(NumberOfDays)
                FROM ta_tbl_LeaveApplication
                WHERE YearSettings_Id=@YearSettingId AND IsDeleted=0
                  AND Status<>'Rejected'
                  AND Type NOT IN ('Restricted Holiday','Comp Off','ESI','LOP')
                  AND Employee_Id=@Employee_Id AND LeaveType_Id=@LeaveType_Id

                IF @SuccesiveLeavepolicyCount IS NULL
                    SET @SuccesiveLeavepolicyCount = 0.0

                SELECT
                    @MaxapplicableDays-@SuccesiveLeavepolicyCount   AS RemainingNoOfLeaves,
                    ISNULL(@LeaveApplicationCount,0)                AS availed,
                    ISNULL(@LOPCount,0)                             AS LOPavailed
            END
        END -- END FIFO=0

    END -- END @LeaveType_Id <> -1


    -- =============================================
    -- ALL LEAVE TYPES (@LeaveType_Id = -1)
    -- Validates per leave type:
    --   PreviousYear Quota (carry_PrevYear)
    --   + CurrentYear Quota (Current_Quota / QuotaDaysCredited)
    --   - CurrentMonth Pro-Rata accrual
    --   - Taken Leaves (availed)
    -- KEY: 3 result sets merged via UNION ALL:
    --   SET 1 → FIFO=1 leave types  (carry_PrevYear + QuotaAccrual - Availed)
    --   SET 2 → FIFO=0 WITH leave applications  (monthly pro-rata)
    --   SET 3 → FIFO=0 WITHOUT leave applications (monthly pro-rata)
    -- =============================================
    ELSE
    BEGIN
        IF (@YearSettingId = @CurrentyearSettingId)
        BEGIN

            -- =============================================
            -- RESULT SET 1: FIFO=1 Leave Types
            -- [Number of Days / year] = carry_PrevYear + SUM(QuotaDaysCredited)
            -- [Number of Days]        = above - LeavesAvailed  (capped at 0)
            -- =============================================
            SELECT
                lt.LeaveType                                                    AS [Leave Type],
                -- Total quota = PreviousYear carry + CurrentYear accrual
                ISNULL(yc.carry_PrevYear, 0) + ISNULL(qa.TotalQuota, 0)        AS [Number of Days / year],
                -- Remaining = Total quota - availed (floor 0)
                CASE
                    WHEN (ISNULL(yc.carry_PrevYear, 0) + ISNULL(qa.TotalQuota, 0)) - ISNULL(avl.TotalAvailed, 0) < 0
                    THEN 0
                    ELSE (ISNULL(yc.carry_PrevYear, 0) + ISNULL(qa.TotalQuota, 0)) - ISNULL(avl.TotalAvailed, 0)
                END                                                             AS [Number of Days]
            FROM TA_Tbl_LeavePolicyDetails LPD
            INNER JOIN TA_Tbl_Emp_LeavePolicy LP
                ON  LP.LeavePolicy_Id  = LPD.Policy_Id
                AND LP.EmployeeId      = @Employee_Id
                AND LP.YearSettingId   = @YearSettingId
            INNER JOIN TA_Tbl_LeaveType lt
                ON  lt.LeaveType_Id    = LPD.LeaveType_Id
            -- Join yearclosure to get carry_PrevYear for FIFO leave types
            LEFT JOIN TA_tbl_Yearclosure yc
                ON  yc.Employee_Id     = @Employee_Id
                AND yc.LeaveTypeId     = LPD.LeaveType_Id
                AND yc.Year_SettingID  = @YearSettingId
            -- Aggregate current year earned quota from accrual table
            LEFT JOIN (
                SELECT
                    LeaveType_Id,
                    SUM(QuotaDaysCredited) AS TotalQuota
                FROM TA_Tbl_LeaveQuotaAccrual
                WHERE Employee_Id     = @Employee_Id
                  AND YearSettings_Id = @YearSettingId
                  AND IsDeleted       = 0
                GROUP BY LeaveType_Id
            ) qa ON qa.LeaveType_Id = LPD.LeaveType_Id
            -- Aggregate availed leaves for current year
            LEFT JOIN (
                SELECT
                    LeaveType_Id,
                    SUM(NumberOfDays) AS TotalAvailed
                FROM Ta_Tbl_LeaveApplication
                WHERE Employee_Id     = @Employee_Id
                  AND YearSettings_Id = @YearSettingId
                  AND Status      NOT IN ('Rejected')
                  AND IsDeleted       = 0
                  AND IsBorrow        = 0
                  AND Type        NOT IN ('Restricted Holiday','Comp Off','ESI','LOP')
                GROUP BY LeaveType_Id
            ) avl ON avl.LeaveType_Id = LPD.LeaveType_Id
            WHERE LP.Emp_LeavePolicyId = @Emp_LeavePolicyId
              AND LPD.FIFO             = 1              -- Only FIFO leave types
              AND LPD.WorkingDuration  > 0
              AND LPD.LeaveDuration    > 0

            UNION ALL

            -- =============================================
            -- RESULT SET 2: FIFO=0 Leave Types
            -- WITH leave applications (monthly pro-rata)
            -- [Number of Days / year] = carry_PrevYear + Current_Quota - Availed - Encashment
            -- [Number of Days]        = ProRataMonthly - Availed - Encashment + carry_PrevYear + PreviousYearBalance
            -- =============================================
            SELECT lt.LeaveType AS [Leave Type],
                (ISNULL(yc.carry_PrevYear,0.0)+ISNULL(yc.Current_Quota,0.0))-(ISNULL(SUM(la.NumberOfDays),0.0)+ISNULL(yc.Encashment,0.0)) AS [Number of Days / year],
                CASE WHEN (CASE WHEN DATEPART(d,@DateofJoining)>15 THEN CASE WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0 WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month END ELSE CASE WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0 WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month END END-(ISNULL(SUM(la.NumberOfDays),0.0)+ISNULL(yc.Encashment,0.0))-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0))<=0 THEN 0
                ELSE CASE WHEN DATEPART(d,@DateofJoining)>15 THEN CASE WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0 WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END * LPD.MaxDays_Month END ELSE CASE WHEN LPD.No_Of_Days<=(CASE WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0 WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month END) THEN LPD.No_Of_Days ELSE CASE WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0 WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month-ISNULL(SUM(la.NumberOfDays),0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END * LPD.MaxDays_Month END END END-(ISNULL(SUM(la.NumberOfDays),0.0)+ISNULL(yc.Encashment,0.0))-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0)+ISNULL(ltd.PreviousYearBalance,0.0) END AS [Number of Days]
            FROM TA_tbl_Yearclosure yc
            INNER JOIN TA_Tbl_YearSetting ys ON yc.Year_SettingID=ys.YearSettings_Id
            INNER JOIN TA_Tbl_LeaveType lt ON lt.LeaveType_Id=yc.LeaveTypeID
            INNER JOIN TA_Tbl_Emp_LeavePolicy LP ON LP.EmployeeId=yc.Employee_Id AND LP.YearSettingId=yc.Year_SettingID
            INNER JOIN TA_Tbl_Emp_LeavePolicyDetails LPD ON LP.LeavePolicy_Id=LPD.Policy_ID AND LP.Emp_LeavePolicyId=LPD.Emp_LeavePolicyId AND LPD.LeaveType_Id=lt.LeaveType_Id
            -- Join master policy to check FIFO flag
            INNER JOIN TA_Tbl_LeavePolicyDetails MLPD ON MLPD.Policy_Id=LP.LeavePolicy_Id AND MLPD.LeaveType_Id=lt.LeaveType_Id
            LEFT OUTER JOIN Ta_Tbl_LeaveApplication la ON la.LeaveType_Id=lt.LeaveType_Id AND la.YearSettings_Id=ys.YearSettings_Id AND la.employee_Id=@Employee_Id
            LEFT OUTER JOIN ta_tbl_emp_LeaveTypedetails LTD ON LP.EmployeeId=LTD.EmployeeId AND LTD.LeaveTypeId=LPD.LeaveType_Id AND yc.Year_SettingID=LTD.YearSettingsId
            WHERE yc.employee_Id=@Employee_Id
              AND ys.YearSettings_Id=@YearSettingId
              AND MLPD.FIFO = 0                            -- Only NON-FIFO leave types
              AND la.employee_Id=@Employee_Id
              AND la.Isdeleted=0 AND la.Status IN ('Pending','Forwarded','Approved')
              AND la.Type NOT IN ('Restricted Holiday','Comp Off','ESI','LOP') AND la.IsBorrow=0
            GROUP BY yc.LeaveTypeId,yc.Carry_PrevYear,yc.Current_Quota,yc.Borrow_NextYear,yc.Encashment,lt.LeaveType_Id,lt.LeaveType,LPD.MaxDays_Month,ys.FromDate,LPD.No_Of_Days,ltd.NoOffLeaveAvailed,ltd.PreviousYearBalance

            UNION ALL

            -- =============================================
            -- RESULT SET 3: FIFO=0 Leave Types
            -- WITHOUT leave applications (monthly pro-rata)
            -- [Number of Days / year] = carry_PrevYear + Current_Quota - Encashment
            -- [Number of Days]        = ProRataMonthly - Encashment + carry_PrevYear + PreviousYearBalance
            -- =============================================
            SELECT lt.LeaveType AS [Leave Type],
                (ISNULL(yc.carry_PrevYear,0.0)+ISNULL(yc.Current_Quota,0.0))-ISNULL(yc.Encashment,0.0) AS [Number of Days / year],
                CASE WHEN (CASE WHEN DATEPART(d,@DateofJoining)>15 THEN CASE WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))<0 THEN 0 WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0) END ELSE CASE WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))<0 THEN 0 WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0) END END-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0))<=0 THEN 0
                ELSE CASE WHEN DATEPART(d,@DateofJoining)>15 THEN CASE WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))<0 THEN 0 WHEN (CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining))<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,DATEADD(MONTH,1,@DateofJoining)),getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0) END ELSE CASE WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))<0 THEN 0 WHEN (CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days THEN LPD.No_Of_Days ELSE CASE WHEN @DateofJoining<=ys.FromDate THEN DATEDIFF(month,ys.fromdate,getdate())+1 ELSE DATEDIFF(month,@DateofJoining,getdate())+1 END *LPD.MaxDays_Month-ISNULL(yc.Encashment,0.0) END END-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0)+ISNULL(ltd.PreviousYearBalance,0.0) END AS [Number of Days]
            FROM TA_tbl_Yearclosure yc
            INNER JOIN TA_Tbl_YearSetting ys ON yc.Year_SettingID=ys.YearSettings_Id
            INNER JOIN TA_Tbl_LeaveType lt ON lt.LeaveType_Id=yc.LeaveTypeID
            INNER JOIN TA_Tbl_Emp_LeavePolicy LP ON LP.EmployeeId=yc.Employee_Id AND LP.YearSettingId=yc.Year_SettingID
            INNER JOIN TA_Tbl_Emp_LeavePolicyDetails LPD ON LP.LeavePolicy_Id=LPD.Policy_ID AND LP.Emp_LeavePolicyId=LPD.Emp_LeavePolicyId AND LPD.LeaveType_Id=lt.LeaveType_Id
            -- Join master policy to check FIFO flag
            INNER JOIN TA_Tbl_LeavePolicyDetails MLPD ON MLPD.Policy_Id=LP.LeavePolicy_Id AND MLPD.LeaveType_Id=lt.LeaveType_Id
            LEFT OUTER JOIN ta_tbl_emp_LeaveTypedetails LTD ON LP.EmployeeId=LTD.EmployeeId AND LTD.LeaveTypeId=LPD.LeaveType_Id AND yc.Year_SettingID=LTD.YearSettingsId
            WHERE yc.employee_Id=@Employee_Id
              AND MLPD.FIFO = 0                            -- Only NON-FIFO leave types
              AND yc.LeaveTypeId NOT IN (
                  SELECT LeaveType_Id FROM Ta_Tbl_LeaveApplication la
                  INNER JOIN TA_Tbl_YearSetting ys2 ON la.YearSettings_ID=ys2.YearSettings_Id
                  WHERE Employee_Id=@Employee_Id AND Isdeleted=0 AND IsBorrow=0
                  AND Type NOT IN ('Restricted Holiday','Comp Off','ESI','LOP')
                  AND Status NOT IN ('Rejected') AND ys2.IsActive=1
              )
              AND ys.YearSettings_Id=@YearSettingId

        END -- END CurrentYear All LeaveTypes

        ELSE IF (@YearSettingId=@SuccessiveYearSettingId)
        BEGIN
            -- Successive year WITH leave applications
            SELECT lt.LeaveType AS [Leave Type],
                (ISNULL(LPD.MaxDays_Applicable,0.0))-(ISNULL(SUM(la.NumberOfDays),0.0)) AS [Number of Days / year],
                LPD.MaxDays_Month,
                CASE WHEN (DATEDIFF(month,@CurrentYearStartDate,getdate())+1)*LPD.MaxDays_Month-(ISNULL(SUM(la.NumberOfDays),0.0))<0 THEN 0
                ELSE (DATEDIFF(month,@CurrentYearStartDate,getdate())+1)*LPD.MaxDays_Month-(ISNULL(SUM(la.NumberOfDays),0.0)) END AS [Number of Days]
            FROM ta_tbl_leavepolicydetails LPD
            INNER JOIN TA_Tbl_Emp_LeavePolicy elp ON elp.LeavePolicy_Id=lpd.Policy_Id
            INNER JOIN TA_Tbl_YearSetting ys ON ys.YearSettings_Id=@CurrentyearSettingId
            INNER JOIN TA_Tbl_LeaveType lt ON lt.LeaveType_Id=LPD.LeaveType_Id
            LEFT OUTER JOIN Ta_Tbl_LeaveApplication la ON la.LeaveType_Id=lt.LeaveType_Id AND la.YearSettings_Id=ys.YearSettings_Id
            INNER JOIN TA_Tbl_Emp_LeavePolicy LP ON LP.EmployeeId=elp.EmployeeId AND LP.YearSettingId=elp.YearSettingId
            WHERE elp.EmployeeId=@Employee_Id AND la.employee_Id=@Employee_Id
              AND ys.YearSettings_Id=@CurrentyearSettingId AND la.Isdeleted=0
              AND la.Status IN ('Pending','Forwarded','Approved')
              AND la.Type NOT IN ('Restricted Holiday','Comp Off','ESI','LOP') AND la.IsBorrow=0
              AND elp.ToDate=(
                  SELECT TOP 1 TA_Tbl_Emp_LeavePolicy.ToDate
                  FROM TA_Tbl_Emp_LeavePolicy
                  INNER JOIN TA_Tbl_YearSetting ON TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid
                  WHERE TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id AND TA_Tbl_YearSetting.IsActive=1
                  ORDER BY TA_Tbl_Emp_LeavePolicy.ToDate DESC
              )
            GROUP BY lt.LeaveType_Id,LPD.MaxDays_Applicable,lt.LeaveType,LPD.MaxDays_Month,ys.FromDate

            UNION

            -- Successive year WITHOUT leave applications
            SELECT lt.LeaveType AS [Leave Type],
                (ISNULL(LPD.MaxDays_Applicable,0.0)) AS [Number of Days / year],
                LPD.MaxDays_Month,
                CASE WHEN (DATEDIFF(month,@CurrentYearStartDate,getdate())+1)*LPD.MaxDays_Month>ELPD.No_Of_Days THEN ELPD.No_Of_Days
                ELSE (DATEDIFF(month,@CurrentYearStartDate,getdate())+1)*LPD.MaxDays_Month END AS [Number of Days]
            FROM ta_tbl_leavepolicydetails LPD
            INNER JOIN TA_Tbl_Emp_LeavePolicy elp ON elp.LeavePolicy_Id=lpd.Policy_Id
            INNER JOIN TA_Tbl_YearSetting ys ON ys.YearSettings_Id=@CurrentyearSettingId
            INNER JOIN TA_Tbl_LeaveType lt ON lt.LeaveType_Id=LPD.LeaveType_Id
            INNER JOIN TA_Tbl_Emp_LeavePolicy LP ON LP.EmployeeId=elp.EmployeeId AND LP.YearSettingId=elp.YearSettingId
            INNER JOIN TA_Tbl_Emp_LeavePolicyDetails ELPD ON LP.Emp_LeavePolicyId=ELPD.Emp_LeavePolicyId AND ELPD.LeaveType_Id=lt.LeaveType_Id AND ELPD.EmpID=LP.EmployeeId
            WHERE elp.EmployeeId=@Employee_Id
              AND LPD.Leavetype_Id NOT IN (
                  SELECT LeaveType_Id FROM Ta_Tbl_LeaveApplication la
                  WHERE la.Employee_Id=@Employee_Id AND Isdeleted=0 AND IsBorrow=0
                  AND Type NOT IN ('Restricted Holiday','Comp Off','ESI','LOP')
                  AND Status NOT IN ('Rejected') AND la.YearSettings_Id=@CurrentyearSettingId
              )
              AND ys.YearSettings_Id=@CurrentyearSettingId
              AND elp.ToDate=(
                  SELECT TOP 1 TA_Tbl_Emp_LeavePolicy.ToDate
                  FROM TA_Tbl_Emp_LeavePolicy
                  INNER JOIN TA_Tbl_YearSetting ON TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid
                  WHERE TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id AND TA_Tbl_YearSetting.IsActive=1
                  ORDER BY TA_Tbl_Emp_LeavePolicy.ToDate DESC
              )
        END

    END -- END All Leave Types

END
GO



-- =============================================
-- TEST EXECUTION
-- =============================================
-- Test single employee quota calculation
-- EXEC Proc_TA_CalculateContinuousLeaveQuota @Employee_Id=49, @LeaveType_Id=9, @YearSettingId=4

-- Test attendance trigger (manual)
-- EXEC Proc_TA_AttendanceAdujstedHours @EmployeeCodeId='MK1005', @SHIFTDATE='2023-09-11', @CompanyId=1

-- Test remaining leave balance
-- EXEC Proc_TA_GetRemainingNumberOfLeavesForSuccessiveYear @Employee_Id=49, @LeaveType_Id=9, @YearSettingId=4

-- Check accrual history for an employee
-- SELECT * FROM TA_Tbl_LeaveQuotaAccrual WHERE Employee_Id=49 ORDER BY BlockNumber

-- =============================================
-- RUN ORDER (First Time Setup)
-- =============================================
-- 1. Run STEP 1  → ALTER TA_Tbl_LeavePolicyDetails
-- 2. Run STEP 2  → ALTER TA_Tbl_Emp_LeavePolicyDetails
-- 3. Run STEP 3  → CREATE TA_Tbl_LeaveQuotaAccrual
-- 4. Run STEP 4  → UPDATE master policy (PolicyDetails_Id=12)
-- 5. Run STEP 5  → SYNC all employee policies from master
-- 6. Run STEP 6  → CREATE Proc_TA_CalculateContinuousLeaveQuota
-- 7. Run STEP 7  → ALTER Proc_TA_AttendanceAdujstedHours
-- 8. Run STEP 8  → CREATE Proc_TA_GetRemainingNumberOfLeavesForSuccessiveYear
-- =============================================



--- taken Leave COUNT
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Alter PROCEDURE Proc_TA_GetEmployeeLeaveTakenDays 
	-- Add the parameters for the stored procedure here
 @EmployeeID as nvarchar(50),    
 @FromDate as nvarchar(15),  
 @ToDate as nvarchar(15) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
		
		SELECT SUM(NumberOfDays) AS TotalLeaveDays
FROM TA_Tbl_LeaveApplication
WHERE Employee_id = @EmployeeID
AND isdeleted = 0
AND Status = 'Approved'
AND (
      (CONVERT(DATETIME, @FromDate, 102) BETWEEN Leave_FromDate AND Leave_ToDate)
   OR (CONVERT(DATETIME, @ToDate, 102) BETWEEN Leave_FromDate AND Leave_ToDate)
   OR (Leave_FromDate BETWEEN CONVERT(DATETIME, @FromDate, 102) AND CONVERT(DATETIME, @ToDate, 102))
   OR (Leave_ToDate BETWEEN CONVERT(DATETIME, @FromDate, 102) AND CONVERT(DATETIME, @ToDate, 102))
)
  
END
GO


USE [JacRijk]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_InsertLeavePolicyDetails]    Script Date: 27-02-2026 14:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Arun vinoth
-- ALTER  date: 13 Jan 2010
-- Description:	Leave Policy details Insert
-- =============================================
ALTER PROCEDURE [dbo].[Proc_TA_InsertLeavePolicyDetails]
	-- Add the parameters for the stored procedure here
		@Policy_Id INT,
		@LeaveType_Id INT,
		@MaxDays_applicable FLOAT=0,
        @MaxDaysPerMonth FLOAT=0,
		@MaxDays_avail FLOAT=0,
		@MinDays_avail FLOAT=0,
		@Spells INT,
		@MaxDays_Acc FLOAT=0,
		@LossOfPay BIT,
		@Max_LOP_Days FLOAT=0,
		@Count_BeforeWeeklyOff BIT,
		@Count_AfterWeeklyOff BIT,
		@Count_Intervening BIT,
		@Borrow_FromNextYear BIT,
		@Carry_ToNextYear BIT,
		@Encashment BIT,
		@Calc_Factor FLOAT=0,
		@MinWorkDays FLOAT=0,
		@WeekDays BIT,
		@WeekOff BIT,
		@Leave BIT,
		@Holidays BIT,
		@ApplicableAfterDays FLOAT,
		@Employee_Id INT,
		@AvailServicePeriod int,
		@FIFO BIT,
		@Calc_WorkDuration FLOAT=0,
		@Calc_LeaveDuration FLOAT=0
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO TA_Tbl_LeavePolicyDetails (Policy_Id,LeaveType_Id,MaxDays_applicable,MaxDays_Month,MaxDays_avail,MinDays_avail,Spells,MaxDays_Acc,LossOfPay,Max_LOP_Days,Count_BeforeWeeklyOff,Count_AfterWeeklyOff,Count_Intervening,Borrow_FromNextYear,Carry_ToNextYear,Encashment,Calc_Factor,MinWorkDays,WeekDays,WeekOff,Leave,Holidays,ApplicableAfterDays,Emp_Id,AvailServicePeriod,FIFO,WorkingDuration,LeaveDuration)
	VALUES (@Policy_Id,@LeaveType_Id,@MaxDays_applicable,@MaxDaysPerMonth,@MaxDays_avail,@MinDays_avail,@Spells,@MaxDays_Acc,@LossOfPay,@Max_LOP_Days,@Count_BeforeWeeklyOff,@Count_AfterWeeklyOff,@Count_Intervening,@Borrow_FromNextYear,@Carry_ToNextYear,@Encashment,@Calc_Factor,@MinWorkDays,@WeekDays,@WeekOff,@Leave,@Holidays,@ApplicableAfterDays,@Employee_Id,@AvailServicePeriod,@FIFO,@Calc_WorkDuration,@Calc_LeaveDuration)
END

